package com.topscomm.pm.xxlJob;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import com.topscomm.cbo.pojo.CboMessageMailEntity;
import com.topscomm.cbo.pojo.CboUserEntity;
import com.topscomm.cbo.service.ICboAttachmentService;
import com.topscomm.cbo.service.ICboMessageMailService;
import com.topscomm.cbo.service.ICboUserService;
import com.topscomm.cbo.service.impl.CboAttachmentService;
import com.topscomm.pm.pojo.PmTaskLogEntity;
import com.topscomm.pm.service.IPmTaskLogService;
import com.topscomm.pub.util.ConvertUtil;
import com.topscomm.pub.util.StringBufferProxy;
import com.xxl.job.core.biz.model.ReturnT;
import com.xxl.job.core.handler.IJobHandler;
import com.xxl.job.core.handler.annotation.JobHandler;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.ui.freemarker.FreeMarkerTemplateUtils;
import org.springframework.web.servlet.view.freemarker.FreeMarkerConfig;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 连续三天未填写工作日志邮件预警(个人和项目经理)
 */
@JobHandler(value = "notFiledJobLogOverdueThreeDays")
@Component
public class NotFiledJobLogNoticeForPmAndPersonal extends IJobHandler {
    @Autowired
    private IPmTaskLogService pmTaskLogService;
    @Autowired
    private ICboMessageMailService cboMessageMailService;
    @Autowired
    private FreeMarkerConfig freeMarkerConfig;
    @Autowired
    private ICboAttachmentService cboAttachmentService;
    @Autowired
    private ICboUserService cboUserService;

    @Override
    public ReturnT<String> execute(String param) throws Exception {
        StringBufferProxy wherSql = new StringBufferProxy();
        // 只查询科远的
        wherSql.appendSingle("AND PMTASKLOG.DEPTCODE LIKE '26%' and PMTASKLOG.LATESTPHASE ='日志填写'");
        Map<String, Object> wherMap = new HashMap<>();
        wherMap.put("whereSql", wherSql.toString());
        List<Map<String, Object>> notFiledJobLogList = pmTaskLogService.getNotFiledJobLogList(wherMap);
        sentToProjectOwner(notFiledJobLogList);
        List<List<List<Map<String, Object>>>> lists = handleListMapForFreemarker(notFiledJobLogList);
        sentToPersonal(lists);
        return ReturnT.SUCCESS;
    }

    /**
     * 发送邮件给项目经理
     *
     * @param notFiledJobLogList
     */
    public void sentToProjectOwner(List<Map<String, Object>> notFiledJobLogList) {
        // 考虑到项目经理收到的数据更多，所以这里不在邮件内容终体现具体工作日志，而是将工作日志作为附件发送。

        // 先根据项目经理进行分组，再根据日志中的人分组，再根据任务分组
        // 用于生成freemark模板
        List<List<List<Map<String, Object>>>> result = new ArrayList<>();
        // 根据项目经理分组
        Map<String, List<Map<String, Object>>> groupByProjectmanagercode = notFiledJobLogList.stream().collect(Collectors.groupingBy(item -> ConvertUtil.convertToString(item.get("PROJECTMANAGERCODE"))));

        // 遍历根据项目经理分好组的结果
        Set<Map.Entry<String, List<Map<String, Object>>>> entries = groupByProjectmanagercode.entrySet();
        for (Map.Entry<String, List<Map<String, Object>>> item : entries) {

            // 1、创建一个workbook，对应一个Excel文件
            XSSFWorkbook workbook = new XSSFWorkbook();
            // 构建给此项目经理发送邮件的实体
            // 查询此项目经理相关信息
            StringBufferProxy whereSql = new StringBufferProxy();
            whereSql.appendSingle("code = '{0}'", item.getKey());
            List<CboUserEntity> cboUserEntities = cboUserService.queryByWhere(whereSql.toString());
            // 新建邮件实体类
            CboMessageMailEntity mailEntity = new CboMessageMailEntity();// 个人
            // 用户id
            mailEntity.setReceiverids(cboUserEntities.get(0).getId()+"");
            mailEntity.setReceivermails(cboUserEntities.get(0).getEmail());
            mailEntity.setSubject("工作日志填写超期通知");
            mailEntity.setContent("工作日志超期未填预警，详情见附件。");
            mailEntity.setIspdf(0);
            CboMessageMailEntity insertEmailEntity = cboMessageMailService.insert(mailEntity);
            // 按照项目进行分组
            Map<String, List<Map<String, Object>>> groupByUserid = item.getValue().stream().collect(Collectors.groupingBy(o -> ConvertUtil.convertToString(o.get("PROJECTID"))));
            Set<Map.Entry<String, List<Map<String, Object>>>> entriesGroupByUserid = groupByUserid.entrySet();
            // 遍历按照项目分好组的数据，每个项目生成一个sheet
            for (Map.Entry<String, List<Map<String, Object>>> temp : entriesGroupByUserid) {
                // 创建sheet
                XSSFSheet xssfSheet = workbook.createSheet(ConvertUtil.convertToString(temp.getValue().get(0).get("PROJECTNAME")));
                // 创建第0行作为表头
                XSSFRow tableHeadRow = xssfSheet.createRow(0);
                // 写入表头数据
                tableHeadRow.createCell(0).setCellValue("项目");
                tableHeadRow.createCell(1).setCellValue("任务");
                tableHeadRow.createCell(2).setCellValue("日志内容");
                tableHeadRow.createCell(3).setCellValue("日期");
                tableHeadRow.createCell(4).setCellValue("责任人");
                // 遍历此用户的超期日志,生成excel列表
                for (int i = 0; i < temp.getValue().size(); i++) {
                    Map<String, Object> tempMap = temp.getValue().get(i);
                    // 创建内容行
                    XSSFRow contentRow = xssfSheet.createRow(i + 1);
                    // 写入内容数据
                    contentRow.createCell(0).setCellValue(ConvertUtil.convertToString(tempMap.get("PROJECTNAME")));
                    contentRow.createCell(1).setCellValue(ConvertUtil.convertToString(tempMap.get("TASKNAME")));
                    contentRow.createCell(2).setCellValue(ConvertUtil.convertToString(tempMap.get("CONTENT")));
                    contentRow.createCell(3).setCellValue(DateUtil.formatDate(ConvertUtil.convertToDate(tempMap.get("CREATEON"))));
                    contentRow.createCell(4).setCellValue(ConvertUtil.convertToString(tempMap.get("USERNAME")));
                }
                // 调整列宽自适应
                //sheet的索引从0开始,获取sheet列数
                int maxColumn = xssfSheet.getRow(0).getPhysicalNumberOfCells();
                for (int columnNum = 0; columnNum <= maxColumn; columnNum++) {
                    int columnWidth = xssfSheet.getColumnWidth(columnNum) / 256;
                    // 遍历列的数据，获取这一列的最长字符串
                    for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                        XSSFRow currentRow;
                        if (xssfSheet.getRow(rowNum) == null) {
                            currentRow = xssfSheet.createRow(rowNum);
                        } else {
                            currentRow = xssfSheet.getRow(rowNum);
                        }
                        if (currentRow.getCell(columnNum) != null) {
                            XSSFCell currentCell = currentRow.getCell(columnNum);
                            if (currentCell.getCellType() == CellType.STRING) {
                                int length = (currentCell.getStringCellValue().getBytes(StandardCharsets.UTF_8).length + currentCell.toString().length()) / 2;
                                if (columnWidth < length) {
                                    columnWidth = length;
                                }
                            }
                        }
                    }
                    //将最长的length*256设为列宽
                    // sheet.setColumnWidth((short)列数,(short)(length*256));
                    xssfSheet.setColumnWidth(columnNum, columnWidth * 258);
                }
            }
            //写入文件
            try {
                File excelFile = new File("/tempExcel/超期日志" + DateUtil.formatDate(new Date()) + ".xlsx");
                workbook.write(new FileOutputStream(excelFile));
                Map<String, Object> attachMap = new HashMap<>();
                attachMap.put("sourceId", insertEmailEntity.getId());
                attachMap.put("sourceType", "OverdueJobLog");
                attachMap.put("attachFileFileName", "超期日志.xlsx");
                // 上传到文件服务器
                Map<String, Object> objectMap = cboAttachmentService.executeUpload(attachMap, excelFile);
                insertEmailEntity.setAttachmentids(ConvertUtil.convertToString(objectMap.get("id")));
                List<String> listField = new ArrayList<>();
                listField.add("attachmentids");
                // 更新邮件中的附件id
                cboMessageMailService.updateFields(insertEmailEntity,listField);
                workbook.close();
                // 清空文件
                FileUtil.del(excelFile);
            } catch (Exception ex) {
                System.out.println(ex.getMessage());
            }
        }

    }

    /**
     * 发送邮件给个人
     *
     * @param listMap
     */
    public void sentToPersonal(List<List<List<Map<String, Object>>>> listMap) throws IOException, TemplateException {
        for (List<List<Map<String, Object>>> item : listMap) {
            // 新建邮件实体类
            CboMessageMailEntity mailEntityForPersonal = new CboMessageMailEntity();// 个人
            // 用户id
            String userid = ConvertUtil.convertToString(item.get(0).get(0).get("userid"));
            mailEntityForPersonal.setReceiverids(userid);
            mailEntityForPersonal.setReceivermails(ConvertUtil.convertToString(item.get(0).get(0).get("email")));
            mailEntityForPersonal.setSubject("工作日志超期通知");
            // 生成邮件内容模板
            Template template = freeMarkerConfig.getConfiguration().getTemplate("notFiledJobLogNotice.ftl");
            Map<String, Object> paramTemplateMap = new HashMap<>();
            paramTemplateMap.put("taskLogList", item);
            String s = FreeMarkerTemplateUtils.processTemplateIntoString(template, paramTemplateMap);
            mailEntityForPersonal.setContent(s);
            mailEntityForPersonal.setIspdf(0);
            cboMessageMailService.insert(mailEntityForPersonal);
        }

    }

    /**
     * 处理数据库中返回的数据列表，用于生成freemark模板
     *
     * @return
     */
    public List<List<List<Map<String, Object>>>> handleListMapForFreemarker(List<Map<String, Object>> notFiledJobLogList) {
        // 用于生成freemark模板
        List<List<List<Map<String, Object>>>> result = new ArrayList<>();
        // 根据usercode分组
        Map<String, List<Map<String, Object>>> groupByUserId = notFiledJobLogList.stream().collect(Collectors.groupingBy(item -> ConvertUtil.convertToString(item.get("USERID"))));
        // 遍历根据用户分好组的结果
        Set<Map.Entry<String, List<Map<String, Object>>>> entries = groupByUserId.entrySet();

        for (Map.Entry<String, List<Map<String, Object>>> item : entries) {
            // 该用户下所有同一个任务下超过三条未填写的日志列表
            List<Map<String, Object>> taskLogList = item.getValue();
            // 根据任务分组
            Map<String, List<Map<String, Object>>> groupByTaskId = taskLogList.stream().collect(Collectors.groupingBy(i -> ConvertUtil.convertToString(i.get("TASKID"))));
            // 循环组装邮件内容
            Set<Map.Entry<String, List<Map<String, Object>>>> entries1 = groupByTaskId.entrySet();
            List<List<Map<String, Object>>> listForHtml = new ArrayList<>();
            for (Map.Entry<String, List<Map<String, Object>>> temp : entries1) {
                List<Map<String, Object>> listForTemplate = new ArrayList<>();
                for (Map<String, Object> o : temp.getValue()) {
                    Map<String, Object> mapForTemplate = new HashMap<>();
                    mapForTemplate.put("projectname", ConvertUtil.convertToString(o.get("PROJECTNAME")));
                    mapForTemplate.put("taskname", ConvertUtil.convertToString(o.get("TASKNAME")));
                    mapForTemplate.put("content", ConvertUtil.convertToString(o.get("CONTENT")));
                    mapForTemplate.put("createon", DateUtil.formatDate(DateUtil.parseDate(StrUtil.toString(o.get("CREATEON")))));
                    mapForTemplate.put("username", ConvertUtil.convertToString(o.get("USERNAME")));
                    mapForTemplate.put("userid", ConvertUtil.convertToString(o.get("USERID")));
                    mapForTemplate.put("email", ConvertUtil.convertToString(o.get("EMAIL")));
                    listForTemplate.add(mapForTemplate);
                }
                listForHtml.add(listForTemplate);
            }
            result.add(listForHtml);
        }
        return result;
    }
}
